using System;
using System.Collections.Generic;
using System.Text;
using MbUnit.Framework;
using System.Data.SqlClient;

namespace Tests
{
    [TestFixture("Basic SQL Server driver functionality")]
    [Author("Kurt Mackey", "kurt@mubble.net")]
    [FixtureCategory("SqlServer")]
    [TestsOn(typeof(SqlBlocks.Drivers.SqlServer))]
    [Importance(TestImportance.Serious)]
    public class SqlServer
    {
        static Dictionary<string, string> databases = new Dictionary<string, string>(StringComparer.CurrentCultureIgnoreCase);

        /// <summary>
        /// Gets a connection string for a local SQL Express database
        /// </summary>
        /// <param name="localDatabaseFileName">Filename of the local database file</param>
        /// <returns>A connection string for use with the SqlClient tools</returns>
        public static string GetConnectionString(string localDatabaseFileName)
        {
            string format = @"Data Source=.\SQLEXPRESS;AttachDbFilename=""{0}"";Integrated Security=True;User Instance=True";
            if (!localDatabaseFileName.EndsWith(".mdf")) localDatabaseFileName += ".mdf";
            System.IO.FileInfo file = new System.IO.FileInfo(localDatabaseFileName);

            if (file.Exists)
            {
                return string.Format(format, file.FullName);
            }
            else
            {
                throw new System.IO.FileNotFoundException("Database file not found", localDatabaseFileName);
            }
        }

        static string DefaultDatabaseFile = "SqlBlocksTest.mdf";
        static string DefaultDatabaseLogFile = "SqlBlocksTest_log.ldf";

        /// <summary>
        /// Clones the template SQL Express database file and returns a connection string
        /// </summary>
        /// <param name="newName">Name for the new database file</param>
        /// <returns>Connection string for the new database</returns>
        public static string GetClonedDatabaseInstance(string newName)
        {
            string dbname = newName + ".mdf";
            string logname = newName + "_log.ldf";

            if (!databases.ContainsKey(newName))
            {
                databases.Add(newName, dbname);
            }

            if (System.IO.File.Exists(dbname))
            {
                return GetConnectionString(dbname);
            }

            if (System.IO.File.Exists(DefaultDatabaseFile))
            {
                System.IO.File.Copy(DefaultDatabaseFile, dbname, true);
                System.IO.File.Copy(DefaultDatabaseLogFile, logname, true);
                return GetConnectionString(dbname);
            }
            else
            {
                throw new System.IO.FileNotFoundException("Database template file not found", DefaultDatabaseFile);
            }
        }

        // Test transaction runners
        // Test reading/writing versions
        // Test schema dumper

        [SetUp("Sets up the database file for use with basic tests")]
        public void EnsureDatabase()
        {
            Console.WriteLine("Checking for BasicTests DB");
            string cn = SqlServer.GetClonedDatabaseInstance("BasicTests");
            Assert.IsNotEmpty(cn);
            SqlConnection connection = new SqlConnection(cn);
            connection.Open();
            connection.Close();

        }

        public static void ClearTestDatabases()
        {
            foreach (string key in databases.Keys)
            {
                DeleteDatabase(key);
            }
        }
        public static void DeleteDatabase(string dbname)
        {
            string cn = SqlServer.GetConnectionString(dbname);
            using (SqlConnection connection = new SqlConnection(cn))
            {
                connection.Open();
                string sql = string.Format("ALTER DATABASE \"{0}\" SET SINGLE_USER WITH ROLLBACK IMMEDIATE; use master; exec sp_detach_db \"{0}\"", connection.Database);
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        Console.WriteLine(ex.Message);
                        throw ex;
                    }
                }
            }
            System.IO.File.Delete(dbname + ".mdf");
            System.IO.File.Delete(dbname + "_log.ldf");
        }

        [Test("Intentionally fails a transaction, checks database state")]
        public void FailedTransaction()
        {
            string cn = SqlServer.GetConnectionString("BasicTests");
            string[] sql = new string[]{ 
                "CREATE TABLE test (id int not null)",
                "INSERT INTO test (id) VALUES('asdf')"
                };

            try
            {
                SqlBlocks.Drivers.SqlServerHelper.ExecuteNonQuery(cn, sql, true);
            }
            catch (SqlException)
            {
                //Good, this failed
            }

            try
            {
                SqlBlocks.Drivers.SqlServerHelper.ExecuteNonQuery(cn, "INSERT INTO test(id) values(0)", false);
            }
            catch (SqlException ex)
            {
                // This should fail with error code -2146232060
                Assert.AreEqual(-2146232060, ex.ErrorCode);
            }
        }
    }
}
